﻿using Dapper;
using HR.Config;
using HR.IService;
using HR.Models;
using System.Data.SqlClient;

namespace HR.Service
{
    public class Third_kindService : IThird_kindService
    {
        private readonly HRDBContext db;
        private readonly IConfiguration configuration;
        private readonly string? constr;

        public Third_kindService(HRDBContext db, IConfiguration configuration)
        {
            this.db = db;
            this.configuration = configuration;
            this.constr = this.configuration["ConnectionStrings"];
        }

        public Task<Paging<config_file_third_kind>> ThirdKindPage()
        {
            return Task.Run(() =>
            {
                List<config_file_third_kind> tabs = db.config_file_third_kinds.ToList();
                Paging<config_file_third_kind> pgtab = new Paging<config_file_third_kind>()
                {
                    tabs = tabs,
                    rows = tabs.Count
                };
                return pgtab;
            });
        }
        /// <summary>
        /// 联级选择器
        /// </summary>
        /// <returns></returns>
        public Task<List<CFFK>> CascaderSele()
        {
            return Task.Run(() =>
            {
                using (SqlConnection conn = new SqlConnection(constr))
                {
                    string sq = "select [first_kind_id] as id,[first_kind_name] as name from [dbo].[config_file_first_kind]";
                    List<CFFK> list = conn.Query<CFFK>(sq).ToList();
                    foreach (CFFK fk in list)
                    {
                        //根据I级ID查询II级
                        string sql = @$"select [second_kind_id] as id,[second_kind_name] as name from [dbo].[config_file_second_kind] where [first_kind_id]='{fk.id}'";
                        fk.children= conn.Query<CFSK>(sql).ToList();
                    }
                    return list;
                }
            });

        }

        public Task<int> AddThirdKind(config_file_third_kind ftk)
        {
            return Task.Run(() =>
            {
                //查询最大的三级ID，递增,长度两位，左侧用0填充
                int tkid = 0;
                int.TryParse(db.config_file_third_kinds.OrderByDescending(ftk => ftk.third_kind_id).ToList()[0].third_kind_id, out tkid);
                string ftkid = (tkid + 1).ToString().PadLeft(2, '0');
                ftk.third_kind_id = ftkid;
                db.config_file_third_kinds.Add(ftk);
                return db.SaveChanges();
            });
        }


        public Task<int> UdpThirdKind(config_file_third_kind ftk)
        {
            return Task.Run(() =>
            {
                config_file_third_kind? cftk = db.config_file_third_kinds.Find(ftk.ftk_id);
                cftk.first_kind_id = ftk.first_kind_id;
                cftk.first_kind_name = ftk.first_kind_name;
                cftk.second_kind_id = ftk.second_kind_id;
                cftk.second_kind_name = ftk.second_kind_name;
                cftk.third_kind_name = ftk.third_kind_name;
                cftk.third_kind_sale_id = ftk.third_kind_sale_id;
                cftk.third_kind_is_retail = ftk.third_kind_is_retail;
                return db.SaveChanges();
            });
        }
        public Task<int> DelThirdKind(short ftkid)
        {
            return Task.Run(() =>
            {
                config_file_third_kind? cftk = db.config_file_third_kinds.Find(ftkid);
                db.config_file_third_kinds.Remove(cftk);
                return db.SaveChanges();
            });
        }

        public Task<List<CFFK>> ConfigFilesFind()
        {
            return Task.Run(() =>
            {
                using (SqlConnection sqlcon = new SqlConnection(constr))
                {
                    string sql1 = "select [first_kind_id] as id,[first_kind_name] as 'name' from config_file_first_kind";
                    List<CFFK> flist = sqlcon.Query<CFFK>(sql1).ToList();
                    foreach (CFFK f in flist)
                    {
                        string sql2 = $@"select second_kind_id as id,second_kind_name as 'name' from config_file_second_kind where first_kind_id = '{f.id}'";
                        List<CFSK> slist = sqlcon.Query<CFSK>(sql2).ToList();
                        f.children = slist;
                        foreach(CFSK s in slist)
                        {
                            string sql3 = $@"select third_kind_id as id,third_kind_name as 'name' from config_file_third_kind where second_kind_id = '{s.id}'";
                            s.children= sqlcon.Query<CFTK>(sql3).ToList();
                        }
                    }
                    return flist;
                }
            });
        }
    }
}
